import pandas as pd
import toml
import json
from sqlalchemy.ext.automap import automap_base
from xunkemgmt_client.market.feed_stats_v2.database.connection.coin2_database import coin2_feed_stats_importer_engine
from sqlalchemy.orm import scoped_session, sessionmaker


registered_mea = ['Spot.Binance.v1', 'Futures.Binance.v1', 'Futures.Bybit.v3','Futures.Okex.v5-swap','Futures.Dydx.v3','Spot.Bithumb.v2','Spot.Gdax.v1','Spot.Bitbank.v1',
                  'Spot.Upbit.v1','Spot.Btcturk.v1 ','Spot.Okex.v5','Spot.Kucoin.v1','Spot.Kraken.v1','Spot.Korbit.v1','Futures.Prex.v1']
registered_mea_05cn = ['Spot.Okex.v5', 'Futures.Okex.v5-swap']
registered_base = ['BTC','ETH', 'XRP', 'ADA', 'DOGE', 'MATIC', 'SOL', 'DOT', 'LTC', 'AVAX', 'SHIB', 'TRX', 'UNI', 'LINK', 'ATOM', 'XMR', 'XLM', 'FIL', 'BCHN', 'APT']
registered_quote = ['USD','USDT','KRW','JPY']

class FeedStatsImporter():
  def __init__(self, *, engine=coin2_feed_stats_importer_engine):
    self._engine = engine

  def _get_session(self):
    return scoped_session(sessionmaker(autocommit=False,
                                       autoflush=False,
                                       bind=self._engine))


class LatencyMetadataImporter(FeedStatsImporter):
  def __init__(self):
    super().__init__()
    base = automap_base()
    base.prepare(autoload_with=self._engine)
    self._LatencyStatsMetadata = base.classes.LatencyStatsMetadata

  def insert_into_db(self, metadata_df):
    session = self._get_session()
    machine = metadata_df.iloc[0]["machine"]
    query = session.query(self._LatencyStatsMetadata).filter(self._LatencyStatsMetadata.machine == machine)
    current_metadata_df = pd.read_sql(query.statement, self._engine)
    tmp_df = pd.concat([metadata_df,current_metadata_df])
    del(tmp_df['id'])
    new_symbol_df = tmp_df.loc[tmp_df.duplicated(keep=False)==False]
    new_symbol_df.to_sql('LatencyStatsMetadata',self._engine, if_exists='append', index=False)

    
def insert_metadata():
    machines = [
      # "feed-02.ap-northeast-2.aws",
      "feed-05.ap-northeast-1.aws",
      "feed-05.cn-hongkong.aliyun",
      # "feed-02.eu-west-1.aws",
      # "feed-02.us-east-1.aws",
      # "feed-01.ap-northeast-1.aws.huobi",
    ]
    symbol_group_path = "./data/coin2/feed/symbol_groups.toml"
    for machine in machines:
      machine_config_path = f"./data/coin2/feed/ops/feed-writer/{machine}.json"
      with open(machine_config_path,"r") as pf:
        subscribed_mea = json.load(pf)["feed"]["exchanges"].keys()

      with open(symbol_group_path,"r") as pf:
        symbol_groups = toml.load(pf)
        symbol_groups = dict((key,value) for key, value in symbol_groups.items() if format_mea(key) in registered_mea and format_mea(key) in subscribed_mea)
        if machine == "feed-05.cn-hongkong.aliyun":
          symbol_groups = dict((key,value) for key, value in symbol_groups.items() if format_mea(key) in registered_mea_05cn and format_mea(key) in subscribed_mea)
      
      latency_matadata_df = pd.DataFrame()
      if(machine != "feed-05.cn-hongkong.aliyun"):
        f_binance_symbol = []
        for symbols in symbol_groups["futures_binance_v1"].values():
          f_binance_symbol.extend([s.split(".")[0] for s in symbols if "PERPETUAL" in s])
        s_binance_symbol = []
        for symbols in symbol_groups["spot_binance_v1"].values():
          s_binance_symbol.extend([s for s in symbols if s in f_binance_symbol and "-USDT" in s])
        f_binance_symbol = [f"{s}.PERPETUAL" for s in s_binance_symbol ]
        
        s_binance_matadata_df = pd.DataFrame({"symbol":s_binance_symbol,"mea":"Spot.Binance.v1"})
        f_binance_matadata_df = pd.DataFrame({"symbol":f_binance_symbol,"mea":"Futures.Binance.v1"})
        latency_matadata_df = pd.concat([s_binance_matadata_df,f_binance_matadata_df])

      for mea,groups in symbol_groups.items():
        if 'binance' in mea:
          continue
        monitor_symbols = []
        for symbols in groups.values():
          for symbol in symbols:
              s = symbol.split('-')
              if "." not in s[1] and s[0] in registered_base and s[1] in registered_quote:
                monitor_symbols.append(symbol)
              elif  ".PERPETUAL" in s[1] and s[0] in registered_base and s[1].replace(".PERPETUAL","") in registered_quote:
                monitor_symbols.append(symbol)
        latency_matadata_df = pd.concat([latency_matadata_df,pd.DataFrame({"symbol":monitor_symbols,"mea":format_mea(mea)})])
      latency_matadata_df["machine"] = machine
      importer = LatencyMetadataImporter()
      importer.insert_into_db(latency_matadata_df)

def format_mea(mea: str):
  mea = mea.split('_')
  return '.'.join([mea[0].title(), mea[1].title(), mea[2].lower()])

if __name__ == '__main__':
  insert_metadata()